﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;

namespace ProjectMadaktariMVC.Models
{
    public class PatientDAO
    {
        private MadaktariDataContext db = new MadaktariDataContext();

        //Query Methods

        /// <summary>
        /// Returns all patients in the system
        /// </summary>
        /// <returns></returns>
        public List<Patient> FindAllPatients()
        {
            return db.Patients.ToList<Patient>();
        }

        /// <summary>
        /// Returns all patients with the given first name in the system
        /// </summary>
        /// <returns></returns>
        public List<Patient> FindPatientByFName(string fname)
        {
            var patients = from patient in db.Patients where patient.fName == fname select patient;
            return patients.ToList<ProjectMadaktariMVC.Models.Patient>();
        }

        /// <summary>
        /// Returns all patients with the given last name in the system
        /// </summary>
        /// <returns></returns>
        public List<Patient> FindPatientByLName(string lname)
        {
            var patients = from patient in db.Patients where patient.lName == lname select patient;
            return patients.ToList<Patient>();
        }

        /// <summary>
        /// Returns all patients with the given phone number in the system
        /// </summary>
        /// <returns></returns>
        public List<Patient> FindPatientByPhone(string phoneNumber)
        {
            var patients = from patient in db.Patients where patient.cellNumber == phoneNumber select patient;
            return patients.ToList<Patient>();
        }

        /// <summary>
        /// Returns all patients with the given record number in the system
        /// </summary>
        /// <returns></returns>
        public List<Patient> FindPatientByRecordNumber(string recordNumber)
        {
            var patients = from patient in db.Patients where patient.recordNumber == recordNumber select patient;
            return patients.ToList<Patient>();
        }

        /// <summary>
        /// Returns all patients with the given procedure date in the system
        /// </summary>
        /// <returns></returns>
        public List<Patient> FindPatientByProcedureDate(DateTime procedureDate)
        {
            var patients = from pro in db.Procedures join epi in db.Episodes on pro.episodeID equals epi.episodeID join pat in db.Patients on epi.patientID equals pat.patientID where pro.date == procedureDate select pat;
            return patients.ToList<Patient>();
        } 

        /// <summary>
        /// Returns a patient with a given id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Patient GetPatient(int id)
        {
            return db.Patients.Single<Patient>(d => d.patientID == id);
        }

        //Find Patient

        /// <summary>
        /// Finds a patient given any number of the 4 paramters and returns a list of those patients.
        /// </summary>
        /// <param name="patient"></param>
        public List<Patient> FindPatient(string fname, string lname, string phoneNumber, string procDate)
        {
            List<IEnumerable<Patient>> tablesList = new List<IEnumerable<Patient>>();
            IEnumerable<Patient> resultsTable = new List<Patient>();

            if (fname != "" && fname != null)
            {
                var fnamePatients = from patient in db.Patients where patient.fName == fname select patient;
                tablesList.Add(fnamePatients);
            }
            if (lname != "" && lname != null)
            {
                var lnamePatients = from patient in db.Patients where patient.lName == lname select patient;
                tablesList.Add(lnamePatients);
            }
            if (phoneNumber != "" && phoneNumber != null)
            {
                var phonePatients = from patient in db.Patients where patient.cellNumber == phoneNumber select patient;
                tablesList.Add(phonePatients);
            }
            if (procDate != "" && procDate != null)
            {
                DateTime procedureDate = DateTime.Parse(procDate);
                var procedurePatients = from pro in db.Procedures join epi in db.Episodes on pro.episodeID equals epi.episodeID join pat in db.Patients on epi.patientID equals pat.patientID where pro.date == procedureDate select pat;
                tablesList.Add(procedurePatients);
            }

            if (tablesList.Count > 0) 
            {
                resultsTable = tablesList[0];
                foreach ( IEnumerable<Patient> p in tablesList) 
                {
                    resultsTable = from pat1 in resultsTable join pat2 in p on pat1.patientID equals pat2.patientID select pat1;
                }

            }

            return resultsTable.ToList();
        }

        //Insert/Delete Methods

        /// <summary>
        /// Adds a patient to the system.  Save() needs to be called afterward for this change to by persisted.
        /// </summary>
        /// <param name="patient"></param>
        public void Add(Patient patient)
        {
            db.Patients.InsertOnSubmit(patient);
        }

        /// <summary>
        /// Removes a patient to the system.  Save() needs to be called afterward for this change to by persisted.
        /// </summary>
        /// <param name="patient"></param>
        public void Delete(Patient patient)
        {
            db.Patients.DeleteOnSubmit(patient);
        }

        /// <summary>
        /// Returns all Procedures in the System
        /// </summary>
        /// <returns></returns>
        public List<Procedure> FindAllProcedures()
        {
            return db.Procedures.ToList<Procedure>();
        }

        /// <summary>
        /// Returns a Procedure with the given id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Procedure GetProcedure(int id)
        {
            return db.Procedures.Single<Procedure>(d => d.procedureID == id);
        }

        //Insert/Delete Methods

        /// <summary>
        /// Adds a new Procedure into the system.  Save() must be called for this change to be persisted
        /// </summary>
        /// <param name="procedure"></param>
        public void Add(Procedure procedure)
        {
            db.Procedures.InsertOnSubmit(procedure);
        }

        /// <summary>
        /// Removes a Procedure from the system. Save() must be called for this change to be persisted
        /// </summary>
        /// <param name="procedure"></param>
        public void Delete(Procedure procedure)
        {
            db.Procedures.DeleteOnSubmit(procedure);
        }

        //Query Methods

        /// <summary>
        /// Returns all Episodes in the system
        /// </summary>
        /// <returns></returns>
        public List<Episode> FindAllEpisodes()
        {
            return db.Episodes.ToList<Episode>();
        }

        /// <summary>
        /// Returns an Episode with a given id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Episode GetEpisode(int id)
        {
            return db.Episodes.Single<Episode>(d => d.episodeID == id);
        }

        //Insert/Delete Methods

        /// <summary>
        /// Adds an Episode to the system.  Save() needs to be called for this change to be persisted.
        /// </summary>
        /// <param name="episode"></param>
        public void Add(Episode episode)
        {
            db.Episodes.InsertOnSubmit(episode);
        }

        /// <summary>
        /// Deletes an Episode from the system.  Save() needs to be called for this change to be persisted.
        /// </summary>
        /// <param name="episode"></param>
        public void Delete(Episode episode)
        {
            db.Episodes.DeleteOnSubmit(episode);
        }


        public bool EpisodeHasDiagnosis(int episodeId, int diagnosisId)
        {
            var count = db.EpisodeDiagnosisRelationships.Count(rel => rel.diagnosisID == diagnosisId && rel.episodeID == episodeId);
            if (count > 0)
                return true;
            return false;
        }

        public void RemoveEpisodeDiagnosisRelationship(EpisodeDiagnosisRelationship rel)
        {
            db.EpisodeDiagnosisRelationships.DeleteOnSubmit(rel);
        }


        //Persistence

        /// <summary>
        /// Saves all changes in the model to the database.
        /// </summary>
        public void Save()
        {
            db.SubmitChanges();
        }

        public void Refresh(Patient patient)
        {
            db.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, patient);
        }
    }
}
